Creating running totals in a one-to-many linking relationship

The term A to B, A to C report is used to refer to any report in which a primary table is linked to two lookup tables (see Methods of looking up tables (direct access databases)). However, in a true A to B, A to C link, a single field in the primary table is used to link to both of the lookup tables.

In a true A to B, A to C relationship, one of the two lookup tables usually has more records than the other. If you group these records based on a field in the primary table, values in the smaller lookup table are repeated for each value in the larger lookup table. The following table shows data for an A to B, A to C relationship. The Customer table is linked to the Credit table and again to the Orders table. Notice that Jones has only one Credit ID, but the credit and its amount are repeated, once for each of Jones' two orders.

Name Credit ID Credit Amount Order ID Order Amount

Jones

1

-10.00

1

10.00

Jones

1

-10.00

2

12.00






Smith

2

-23.00

3

20.00

Smith

3

-45.00

4

30.00

In this example, a standard subtotal is used for both the Order Amount field and the Credit Amount field. Thus, Jones' single credit is counted twice, and the subtotal displays an inaccurate value of -20.00.

Name Credit ID Credit Amount Order ID Order Amount

Jones

1

-10.00

1

10.00

Jones

1

-10.00

2

12.00



-20.00


22.00






Smith

2

-23.00

3

20.00

Smith

3

-45.00

4

30.00



-68.00


50.00

Note:    Notice that the total Credit Amount for Jones is incorrect.

This problem also occurs in the Order Amount field if Jones had two different Credit Amounts and only one Order Amount. Avoid this problem by creating a running total for each field you want subtotaled.



Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com